package com.example.zhinongbao.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import android.util.Log;

import com.example.zhinongbao.entity.UserInfo;

import org.jetbrains.annotations.Nullable;

public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBHelper";
    private static final String DATABASE_NAME = "znb.db";// 数据库名
    private static final int DATABASE_VERSION = 1;// 数据库版本号
    public static final String TABLE_NAME_GOODS = "goods"; //商品表
    public static final String TABLE_NAME_USER_TABLE = "user_table"; //用户表
    public static final String TABLE_NAME_ORDER = "order2"; // 订单表 order是关键字
    public static final String TABLE_NAME_SETTINGS = "settings"; // 设置表
    public static final String TABLE_NAME_PHOTO = "photo";
    public static final String TABLE_NAME_MESSEGES = "messeges";
    private static DBHelper sHelper;

    public DBHelper(@Nullable Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public synchronized static DBHelper getInstance(Context context) {
        if (null == sHelper) {
            sHelper = new DBHelper(context);
        }
        return sHelper;
    }

    //数据库创建时被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        initTable(db);
        initData(db);
    }

    //数据库升级
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) { // 数据库 1 -> 2 升级
            // 创建设置表
            db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " (keycolumn text primary key,valuecolumn text)");
        }
    }

    /**
     * 初始化数据表
     *
     * @param db 数据库
     */
    private void initTable(SQLiteDatabase db) {
        // 创建商品表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_GOODS + " (" +
                "_id integer primary key autoincrement," +
                "goods_name text," +
                "goods_price double," +
                "goods_unit text," +
                "goods_browse integer," +
                "goods_message text)");
        // 创建用户表
//        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USEROO + " (" +
//                "_id integer primary key autoincrement," +
//                "user_name text," +
//                "user_password text)");
        // 创建订单表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ORDER + " (" +
                "_id integer primary key autoincrement," +
                "order_name text," +
                "order_number  bigint," +
                "user_id  integer," +
                "FOREIGN KEY (user_id) REFERENCES user(_id))");
        // 创建图片表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PHOTO + " (" +
                "_id integer primary key autoincrement," +
                "goods_id  integer," +
                "image BLOB," +
                "FOREIGN KEY (goods_id) REFERENCES goods(_id))");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_MESSEGES + " (" +
                "messege_id integer primary key autoincrement," +
                "messege_body text)");
        //创建user_table表
//        db.execSQL("create table user_table(user_id integer primary key autoincrement, " +
//                "username text," +       //用户名
//                "age int," +
//                "phone int," +
//                "address text," +
//                "password text," + //密码
//                "nickname text" +       // 注册类型   0---用户   1---管理员
//                ")");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USER_TABLE + "(" +
                "user_id integer , "+
                "username text primary key  ," +       //用户名
                "age int," +
                "phone int," +
                "address text," +
                "concern text," +
                "paypassword text," +
                "password text," + //密码
                "nickname text" +       // 注册类型   0---用户   1---管理员
                ")");

    }


    /**
     * 初始化数据库内部模拟数据(仅在第一次安装时执行)
     *
     * @param db 数据库
     */
    private void initData(SQLiteDatabase db) {
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('四川峨眉山黄瓜', 1.23, '斤', '123', '来自四川雪山之巅的黄瓜。')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('东北大米', 3.33, '斤', '444', '来自东北的大米，煮饭特别香。')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('海南香蕉', 9.99, '斤', '213', '海南香蕉，特别好吃')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('新疆又香又甜的哈密瓜', 5.99, '斤', '333', '又大又甜，特别好吃')");
        db.execSQL("insert into " + TABLE_NAME_GOODS +
                " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
                "values" +
                "('江苏黄瓜', 1.12, '斤', '223', '来自江苏的黄瓜。')");

//        db.execSQL("insert into " + TABLE_NAME_USER_TABLE +
//                " ( paypassword) " +
//                "values" +
//                "('1234')");
//        db.execSQL("insert into " + TABLE_NAME_USER +
//                " (user_name , user_password) " +
//                "values" +
//                "('2', '2')");
//        db.execSQL("insert into " + TABLE_NAME_USER +
//                " (user_name , user_password) " +
//                "values" +
//                "('3', '3')");
//        db.execSQL("insert into " + TABLE_NAME_MESSEGES +
//                "(messege_id ,messege_body)" +
//                "values" +
//                "('1','你好，我想了解一下你这个商品可以吗')");
    }

    /**
     * 初始化数据库内部模拟数据(仅在第一次安装时执行)
     *
     * @param db 数据库
     */
    //dengli

    /**
     * 登录  根据用户名查找用户
     */
    @SuppressLint("Range")
    public UserInfo login(String username) {
        //获取SQLiteDatabase实例
        SQLiteDatabase db = getReadableDatabase();
        UserInfo userInfo = null;
        String sql = "select user_id,username,password,nickname from user_table where username=?";
        String[] selectionArgs = {username};
        Cursor cursor = db.rawQuery(sql, selectionArgs);
        if (cursor.moveToNext()) {
            String user_id = cursor.getString(cursor.getColumnIndex("user_id"));
            String password = cursor.getString(cursor.getColumnIndex("password"));
            String nickname = cursor.getString(cursor.getColumnIndex("nickname"));

            userInfo = new UserInfo(user_id, password, nickname,username);
        }
        cursor.close();
        db.close();
        return userInfo;
    }
//    @SuppressLint("Range")
//    public UserInfo getUserByName(String username){
//        SQLiteDatabase db = getReadableDatabase() ;
//        UserInfo userInfo = null;
//        String sql = "select user_id,password,nickname,phone,age,address,paypassword,concern from user_table where username=?";
//        String[] selectionArgs = {username};
//        Cursor cursorr = db.rawQuery(sql, selectionArgs);
//        if (cursorr.moveToNext()) {
//            String user_id = cursorr.getString(cursorr.getColumnIndex("user_id"));
//
//            String password = cursorr.getString(cursorr.getColumnIndex("password"));
//            String nickname = cursorr.getString(cursorr.getColumnIndex("nickname"));
//            String phone = cursorr.getString(cursorr.getColumnIndex("phone"));
//            int age = cursorr.getInt(cursorr.getColumnIndex("age"));
//            String address = cursorr.getString(cursorr.getColumnIndex("address"));
//            String paypassword =cursorr.getString(cursorr.getColumnIndex("paypassword"));
//            String concern =cursorr.getString(cursorr.getColumnIndex("concern"));
//             userInfo = new UserInfo(user_id, username, password, nickname, age, address, phone,paypassword,concern);
//    }
//        cursorr.close();
//        db.close();
//        return userInfo;
//    }


    //zhuce
    public int register(String username, String password, String phone,String paypassword) {
        //获取SQLiteDatabase实例
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        //填充占位符
        values.put("username", username);
        values.put("password", password);
        values.put("phone", phone);
        values.put("paypassword",paypassword);
        String nullColumnHack = "values(null,?,?,?)";
        //执行
        int insert = (int) db.insert("user_table", nullColumnHack, values);
        db.close();
        return insert;
    }


}